import pandas as pd


def detail(path):
    # 读取Excel文件
    df = pd.read_excel(path)

    # 将学号列设置为文本类型
    df['学号'] = df['学号'].astype(str)

    # 添加计算列
    df['不及格科目数'] = df['总成绩'].apply(lambda x: 1 if x < 60 else 0)
    df['不及格科目名称'] = df.apply(lambda row: row['课程名称'] + ', ' if row['不及格科目数'] == 1 else '', axis=1)

    # 按学号和班级分组，汇总数据
    result_df = df.groupby(['学号', '姓名', '当前所在班级']).agg({
        '不及格科目数': 'sum',
        '不及格科目名称': lambda x: ''.join(x)
    }).reset_index()

    # 筛选不及格的学生
    result_df = result_df[result_df['不及格科目数'] > 0]

    # 重命名列
    result_df.columns = ['学号', '姓名', '班级', '不及格科数', '不及格科目']

    # 保存结果到新的Excel文件
    return result_df


def percentage(path):
    # 读取Excel文件
    df = pd.read_excel(path)

    # 根据课程编号和成绩标志筛选不及格的记录
    failures = df[df['总成绩'] < 60]

    # 统计每门课程的不及格人数
    failures_count = failures.groupby('课程编号')['学号'].count().reset_index()
    failures_count.columns = ['课程编号', '挂科人数']

    # 统计每门课程的选修人数
    total_students = df.groupby('课程编号')['学号'].nunique().reset_index()
    total_students.columns = ['课程编号', '选修人数']

    # 合并两个统计结果
    result = pd.merge(failures_count, total_students, on='课程编号')

    # 合并课程名称到结果中
    result = pd.merge(result, df[['课程编号', '课程名称']].drop_duplicates(), on='课程编号')

    # 计算挂科率（以百分数形式展示）
    result['挂科率'] = (result['挂科人数'] / result['选修人数']) * 100

    # 将挂科率列保存为百分数格式
    result['挂科率'] = result['挂科率'].apply(lambda x: f"{x:.2f}%")

    # 保存结果到Excel文件
    return result[['课程名称', '挂科人数', '选修人数', '挂科率']]


if __name__ == '__main__':
    detail_excel = detail('数据/2022-2023-2成绩信息（2021级）.xls')
    percentage_excel = percentage('数据/2022-2023-2成绩信息（2021级）.xls')
    # 创建一个 ExcelWriter 对象，指定文件名
    with pd.ExcelWriter('数据/挂科明细视图.xlsx') as writer:
        # 将 df1 写入工作表明细
        detail_excel.to_excel(writer, sheet_name='明细', index=False)
        # 将 df2 写入工作表挂科率
        percentage_excel.to_excel(writer, sheet_name='挂科率', index=False)
    print('文件已生成!')
